SET NOCOUNT ON;
go
/*********************************************************************************************************
*Script Developer:sfibich
*Script Name:Get_StatsOverview.sql
*Script Version:1.0.2013.09.01
*Tester:
*Tested on SQL Versions:2008, 2008R2
*Script Description:Gives the basic statitics information.
*					 
*********************************************************************************************************/
go
select
OBJECT_SCHEMA_NAME(s.object_id) as [schema_name] 
,OBJECT_NAME(s.object_id) as [object_name]
,s.name as [stats_name]
,CAST((
	select c.name as [name] from sys.stats_columns sc 
	inner join
	sys.columns c
	on
	sc.column_id = c.column_id 
	and
	sc.object_id = c.object_id 
	where
	sc.object_id =s.object_id 
	and
	sc.stats_id =s.stats_id 
	order by sc.stats_column_id asc
	FOR XML RAW('col')
) as XML) as [stats_columns]
,STATS_DATE(s.object_id,s.stats_id) as [stats_date]
,s.filter_definition 
,s.no_recompute 
,case when s.auto_created  = 1 then 'Auto'
	when s.user_created = 1 then 'User'
	else 'Index' 
end as [type]
from
sys.stats s
where
OBJECT_SCHEMA_NAME(s.object_id) not in ('sys')
order by
OBJECT_SCHEMA_NAME(s.object_id)
,s.name